package zunge.lbe.datacollect;

import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.HashSet;
import java.util.List;

import zunge.lbe.datacollect.Lbe.UserDataInfo;
import zunge.tools.DbConn;
import zunge.tools.SHA512Hash;
import zunge.tools.Tools;


public class BlobParser {
	public static final int P = 12;
	public static final int AMIN = 5;
	
	static final int COMMIT = 500; //SQL commit
	
	
	public static void createTables(DbConn connOut) throws Exception{
		//Create tables: record_uap, u, a, mutex,
		String query = "create table u (u_id bigint auto_increment primary key, u_name varchar(128) not null unique ) ";
		connOut.conn.prepareStatement(query).execute();
		query = "create table a (a_id bigint auto_increment primary key, a_name varchar(300) not null unique ) ";
		connOut.conn.prepareStatement(query).execute();
		query = "create table mutex (i int primary key); ";
		connOut.conn.prepareStatement(query).execute();
		query = "insert into mutex (i) values (1); ";
		connOut.conn.prepareStatement(query).execute();
		query = "create table record_uap "
							+ "(id bigint auto_increment primary key, "
							+ "u_id bigint not null, a_id bigint not null, p_id int not null, "
							+ "time datetime not null, decision int not null, index(u_id, a_id, p_id) );";
		connOut.conn.prepareStatement(query).execute();
	}
	
	
	/** Read data dumps and store into the output database */
	public static void parseBlobsDict(DbConn connIn, int dateInt, DbConn connOut) throws Exception{
		System.out.println("parseBlobs dict "+dateInt);
		String tableBlob = "lbe_backend_userdatainfo_original_"+dateInt;
		insertDict(tableBlob, connIn, connOut);
	}
	public static void parseBlobsDetail(DbConn connIn, int dateInt, DbConn connDict, DbConn connOut) throws Exception{
		System.out.println("parseBlobs detail "+dateInt);
		String tableBlob = "lbe_backend_userdatainfo_original_"+dateInt;
		insertDetails(tableBlob, connIn, connDict, connOut);
	}
	
	
	
	static void insertDict(String tableBlob, DbConn connIn, DbConn connOut) throws Exception{
		DictManager dictManager = new DictManager(connOut);
		String query = "select data from "+tableBlob;
		ResultSet rs = connIn.executeQueryInStream(query);
		
		while(rs.next()){
			Blob blob = rs.getBlob(1);
			byte[] bytes = blob.getBytes(1, (int)blob.length());
			UserDataInfo udi = UserDataInfo.parseFrom(bytes);
			
			int aCount = udi.getAppListCount();
			if(aCount<AMIN){continue;}
			
			String u_name = udi.getImei();
			dictManager.newUserName(u_name);
			for(int ai = 0;ai < aCount;++ai){
				UserDataInfo.UserPackageInfo upi = udi.getAppList(ai);
				String a_name = upi.getPackageName();
				dictManager.newAppName(a_name);
			}
			blob.free();
		}
		dictManager.refresh();
		rs.close();
	}
	
	
	
	
	
	
	static void insertDetails(String tableBlob, DbConn connIn, DbConn connDict, DbConn connOut) throws Exception{
		DictManager dictManager = new DictManager(connDict);
		PreparedStatement ps_record_uap = connOut.getPreparedStatement("insert into record_uap (u_id, a_id, p_id, time, decision)" +
				" values (?,?,?,?,?)");
		
		//Faster read
		connDict.conn.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
		
		String query = "select create_time_ymd, create_time_hms, data from "+tableBlob;
		ResultSet rs = connIn.executeQueryInStream(query);
		while(rs.next()){
			
			String datetime = Tools.formatDatetime(rs.getString(1), rs.getString(2));
			
			Blob blob = rs.getBlob(3);
			byte[] bytes = blob.getBytes(1, (int)blob.length());
			UserDataInfo udi = UserDataInfo.parseFrom(bytes);
			
			int aCount = udi.getAppListCount();
			if(aCount<AMIN){continue;}
			
			String u_name = udi.getImei();
			//String u_name = SHA512Hash.hash(udi.getImei());
			long u_id = dictManager.getUser(u_name);
			
			for(int ai = 0;ai < aCount;++ai){
				UserDataInfo.UserPackageInfo upi = udi.getAppList(ai);
				
				String a_name = upi.getPackageName();
				long a_id = dictManager.getApp(a_name);
				
				int p_mask = (int)upi.getPermissionsMask();
				int p_accept = (int)upi.getPermissionsAccept();
				int p_reject = (int)upi.getPermissionsReject();
				int p_prompt = (int)upi.getPermissionsPrompt();
				if(p_mask>0){
					for(int p = 0;p < P;++p){
						if(Tools.getNBitFromRight(p_mask, 12-p)){ // The pth permission is masked
							//Decision: 0=default, 1=accept, 2=reject, 3=prompt
							int decision = 0;
							if(Tools.getNBitFromRight(p_accept, 12-p)){decision = 1;}
							else if(Tools.getNBitFromRight(p_reject, 12-p)){decision = 2;}
							else if(Tools.getNBitFromRight(p_prompt, 12-p)){decision = 3;}
							
							ps_record_uap.setLong(1, u_id);
							ps_record_uap.setLong(2, a_id);
							ps_record_uap.setInt(3, p);
							ps_record_uap.setString(4, datetime);
							ps_record_uap.setInt(5, decision);
							
							connOut.executeUpdate(ps_record_uap);
						}
					}
				}
			}
			blob.free();
		}
		rs.close();
		
		connDict.conn.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
	}
	
	
	static class DictManager{
		static final String query_insert_u = "insert ignore into u (u_name) values (?)";
				//"insert into u (u_name) select ? from mutex left outer join u on u.u_name = ? where mutex.i = 1 and u.u_name is null";
		static final String query_insert_a = "insert ignore into a (a_name) values (?)";
				//"insert into a (a_name) select ? from mutex left outer join a on a.a_name = ? where mutex.i = 1 and a.a_name is null";
		static final String query_select_u = "select u_id from u where u_name = ?";
		static final String query_select_a = "select a_id from a where a_name = ?";
				
		DbConn conn;
		PreparedStatement ps_insert_u;
		PreparedStatement ps_insert_a;
		PreparedStatement ps_select_u;
		PreparedStatement ps_select_a;
		
		public static final int BUFFER_SET_SIZE = 20000;
		HashSet<String> uNameBuffer = new HashSet<String>();
		HashSet<String> aNameBuffer = new HashSet<String>();
		
		public DictManager(DbConn conn) throws Exception{
			this.conn = conn;
			ps_insert_u = conn.getPreparedStatement(query_insert_u);
			ps_insert_a = conn.getPreparedStatement(query_insert_a);
			ps_select_u = conn.getPreparedStatement(query_select_u);
			ps_select_a = conn.getPreparedStatement(query_select_a);
		}
		public void newUserName(String uName) throws Exception{
			if(!uNameBuffer.contains(uName)){
				uNameBuffer.add(uName);
				if(uNameBuffer.size()>BUFFER_SET_SIZE){refreshUNameBuffer();}
			}
		}
		public void newAppName(String aName) throws Exception{
			if(!aNameBuffer.contains(aName)){
				aNameBuffer.add(aName);
				if(aNameBuffer.size()>BUFFER_SET_SIZE){refreshANameBuffer();}
			}
		}
		public void refresh() throws Exception{
			refreshUNameBuffer();
			refreshANameBuffer();
		}
		void refreshUNameBuffer() throws Exception{
			conn.conn.setAutoCommit(false);
			for(String uName:uNameBuffer){
				submitUserName(uName);
			}
			conn.conn.commit();
			conn.conn.setAutoCommit(true);
			uNameBuffer.clear();
		}
		void refreshANameBuffer() throws Exception{
			conn.conn.setAutoCommit(false);
			for(String aName:aNameBuffer){
				submitAppName(aName);
			}
			conn.conn.commit();
			conn.conn.setAutoCommit(true);
			aNameBuffer.clear();
		}
		
		void submitUserName(String uName) throws Exception{
			ps_insert_u.setString(1, uName);
			//ps_insert_u.setString(2, uName);
			ps_insert_u.execute();
		}
		void submitAppName(String aName) throws Exception{
			ps_insert_a.setString(1, aName);
			//ps_insert_a.setString(2, aName);
			ps_insert_a.execute();
		}
		
		
		
		
		
		
		
		public long getUser(String uName) throws Exception{
			ps_select_u.setString(1, uName);
			ResultSet rs = ps_select_u.executeQuery();
			rs.next();
			long u_id = rs.getLong(1);
			rs.close();
			return u_id;
		}
		public long getApp(String aName) throws Exception{
			ps_select_a.setString(1, aName);
			ResultSet rs = ps_select_a.executeQuery();
			rs.next();
			long a_id = rs.getLong(1);
			rs.close();
			return a_id;
		}
	}
}
